﻿using BCCommon;
using BCCommon.Enums;
using BCEntity.Sim.Admin.Admin;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace BCData.Sim.Admin.Admin
{
    public class AdminData : IAdminData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT AdminId,LoginName,Password,FullName,IsSystemAccount,AccountStatus,CreateTime,Avatar,Phone,HomeAddress,CompanyId,CompanyName,
                                            IdCard,Introduction,Avatar,Sex,Married,EmergencyContact,EmergencyPhone,FamilyNumber,ChildrenNumber,DepartmentName,DepartmentId,
                                            Education,School,GraduationTime,WorkingYears,EnglishLevel,Trainee,CompanyId,CompanyName,JobDutyKey,JobTitleKey,
                                            JobDuty,JobTitle,JobLevel,EmployedDate,LeaveTime,EnglishReadLevel,EnglishWriteLevel,EnglishConversationLevel,Salary,
                                            FullTime,SalaryCard,MaternityLeave,MaternityLeaveStartTime,MaternityLeaveEndTime,
                                            MaternityLeaveSalary,AnnualLeave,SurplusAnnualLeave,Assignment,AssignmentSubsidy,
                                            AssignmentStartTime,AssignmentEndTime,Preference,LastLoginTime,CurrentlyLoginTime,LogonIp,AddressIp,
                                            SecurityAmount,PaidSecurityAmount,
                                            ChildrenEducationCutAmount,ChildrenEducationStartDate,ChildrenEducationEndDate,
                                            ContinueEducationCutAmount,ContinueEducationStartDate,ContinueEducationEndDate,
                                            MedicalCutAmount,HomeLoanCutAmount,HomeRentCutAmount,ElderlyCareCutAmount
                                        FROM Admin";
        private const string QUERY_A = @" SELECT
	                                        A.AdminId,A.LoginName,A.PASSWORD,A.FullName,A.IsSystemAccount,A.AccountStatus,A.CreateTime,A.Avatar,A.Phone,A.HomeAddress,A.CompanyId,
	                                        A.CompanyName,
	                                        A.IdCard,
	                                        A.Introduction,
	                                        A.Avatar,
	                                        A.Sex,
	                                        A.Married,
	                                        A.EmergencyContact,
	                                        A.EmergencyPhone,
	                                        A.FamilyNumber,
	                                        A.ChildrenNumber,
	                                        A.DepartmentName,
	                                        A.DepartmentId,
	                                        A.Education,
	                                        A.School,
	                                        A.GraduationTime,
	                                        A.WorkingYears,
	                                        A.EnglishLevel,
	                                        A.Trainee,
	                                        A.CompanyId,
	                                        A.CompanyName,
	                                        A.JobDutyKey,
	                                        A.JobTitleKey,
	                                        A.JobDuty,
	                                        A.JobTitle,
	                                        A.JobLevel,
	                                        A.EmployedDate,
	                                        A.LeaveTime,
	                                        A.EnglishReadLevel,
	                                        A.EnglishWriteLevel,
	                                        A.EnglishConversationLevel,
	                                        A.Salary,
	                                        A.FullTime,
	                                        A.SalaryCard,
	                                        A.MaternityLeave,A.MaternityLeaveStartTime,A.MaternityLeaveEndTime,A.MaternityLeaveSalary,
	                                        A.AnnualLeave,A.SurplusAnnualLeave,
	                                        A.Assignment,
	                                        A.AssignmentSubsidy,
	                                        A.AssignmentStartTime,
	                                        A.AssignmentEndTime,
	                                        A.Preference,
	                                        A.LastLoginTime,
	                                        A.CurrentlyLoginTime,
	                                        A.LogonIp,
	                                        A.AddressIp,
	                                        A.SecurityAmount,
	                                        A.PaidSecurityAmount,
	                                        A.ChildrenEducationCutAmount,
	                                        A.ChildrenEducationStartDate,
	                                        A.ChildrenEducationEndDate,
	                                        A.ContinueEducationCutAmount,
	                                        A.ContinueEducationStartDate,
	                                        A.ContinueEducationEndDate,
	                                        A.MedicalCutAmount,
	                                        A.HomeLoanCutAmount,
	                                        A.HomeRentCutAmount,
	                                        A.ElderlyCareCutAmount
                                        FROM
	                                        Admin A";
        public AdminData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        
        public async Task<bool> CheckAdminExists(string loginName)
        {
            string sql = @"SELECT 1 FROM Admin WHERE LoginName=?LoginName And AccountStatus='" + AccountStatus.Using.ToString() + "'";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?LoginName", loginName, DbType.AnsiString, size: 30);
            object obj = await databaseContext.Slave.ExecuteScalarAsync(sql, parameters);
            return obj != null;
        }
        
        public async Task<bool> CheckAdminExists(long adminId)
        {
            string sql = @"SELECT 1 FROM Admin WHERE AdminId=?AdminId And AccountStatus='" + AccountStatus.Using.ToString() + "'";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            object obj = await databaseContext.Slave.ExecuteScalarAsync(sql, parameters);
            return obj != null;
        }

        #region 添加员工信息
        public async Task<AdminEntity> Add(AdminEntity adminEntity)
        {
            string sql = SqlCommands.GetInsertSql("Admin", new[] { "LoginName", "Password", "FullName", "CompanyId", "CompanyName","AccountStatus","DepartmentName","DepartmentId",
                "Phone", "HomeAddress","IdCard","Introduction","Avatar","Sex","Married","EmergencyContact","EmergencyPhone","FamilyNumber","ChildrenNumber",
                "Education","School","GraduationTime","WorkingYears","EnglishLevel","Trainee","JobDuty","JobTitle","JobLevel","JobDutyKey","JobTitleKey","EmployedDate","LeaveTime",
                "EnglishReadLevel","EnglishWriteLevel","EnglishConversationLevel","Salary","FullTime","SalaryCard","MaternityLeave","MaternityLeaveStartTime",
                "MaternityLeaveEndTime","MaternityLeaveSalary","AnnualLeave","SurplusAnnualLeave","Assignment","AssignmentSubsidy",
                "AssignmentStartTime","AssignmentEndTime","Preference","LogonIp","AddressIp"  ,"SecurityAmount","PaidSecurityAmount",
                "ChildrenEducationCutAmount","ChildrenEducationStartDate","ChildrenEducationEndDate","ContinueEducationCutAmount",
                "ContinueEducationStartDate","ContinueEducationEndDate","MedicalCutAmount","HomeLoanCutAmount",
                "HomeRentCutAmount","ElderlyCareCutAmount"});
            sql = string.Format(@"{0} {1} WHERE AdminId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?LoginName", adminEntity.LoginName, DbType.AnsiString, size: 30);
            parameters.Add("?Password", adminEntity.Password, DbType.AnsiString, size: 255);
            parameters.Add("?FullName", adminEntity.FullName, DbType.AnsiString, size: 20);
            parameters.Add("?CompanyId", adminEntity.CompanyId, DbType.Int32, size: 10);
            parameters.Add("?CompanyName", adminEntity.CompanyName, DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentId", adminEntity.DepartmentId, DbType.Int32, size: 10);
            parameters.Add("?DepartmentName", adminEntity.DepartmentName, DbType.AnsiString, size: 50);
            parameters.Add("?AccountStatus", AccountStatus.Using.ToString(), DbType.AnsiString);
            parameters.Add("?Phone", adminEntity.Phone, DbType.AnsiString, size: 20);
            parameters.Add("?HomeAddress", adminEntity.HomeAddress, DbType.AnsiString, size: 200);
            parameters.Add("?IdCard", adminEntity.IdCard, DbType.AnsiString, size: 18);
            parameters.Add("?Introduction", adminEntity.Introduction, DbType.String);
            parameters.Add("?Avatar", adminEntity.Avatar, DbType.AnsiString, size: 1000);
            parameters.Add("?Sex", adminEntity.Sex.ToString(), DbType.AnsiString);
            parameters.Add("?Married", adminEntity.Married, DbType.Boolean);
            parameters.Add("?EmergencyContact", adminEntity.EmergencyContact, DbType.AnsiString, size: 30);
            parameters.Add("?EmergencyPhone", adminEntity.EmergencyPhone, DbType.AnsiString, size: 20);
            parameters.Add("?FamilyNumber", adminEntity.FamilyNumber, DbType.Int32);
            parameters.Add("?ChildrenNumber", adminEntity.ChildrenNumber, DbType.Int32);
            parameters.Add("?Education", adminEntity.Education.HasValue ? adminEntity.Education.Value.ToString() : EducationDegree.None.ToString(), DbType.AnsiString);
            parameters.Add("?School", adminEntity.School, DbType.AnsiString, size: 50);
            parameters.Add("?GraduationTime", adminEntity.GraduationTime, DbType.DateTime);
            parameters.Add("?WorkingYears", adminEntity.WorkingYears, DbType.Decimal);
            parameters.Add("?EnglishLevel", adminEntity.EnglishLevel.ToString(), DbType.AnsiString);
            parameters.Add("?Trainee", adminEntity.Trainee, DbType.Boolean);
            parameters.Add("?JobDuty", adminEntity.JobDuty, DbType.AnsiString, size: 50);
            parameters.Add("?JobTitle", adminEntity.JobTitle, DbType.AnsiString, size: 50);
            parameters.Add("?JobDutyKey", adminEntity.JobDutyKey, DbType.AnsiString, size: 150);
            parameters.Add("?JobTitleKey", adminEntity.JobTitleKey, DbType.AnsiString, size: 150);
            parameters.Add("?JobLevel", adminEntity.JobLevel, DbType.Int32);
            parameters.Add("?EmployedDate", adminEntity.EmployedDate, DbType.DateTime);
            parameters.Add("?LeaveTime", adminEntity.LeaveTime, DbType.DateTime);
            parameters.Add("?EnglishReadLevel", adminEntity.EnglishReadLevel, DbType.Int32);
            parameters.Add("?EnglishWriteLevel", adminEntity.EnglishWriteLevel, DbType.Int32);
            parameters.Add("?EnglishConversationLevel", adminEntity.EnglishConversationLevel, DbType.Int32);
            parameters.Add("?Salary", adminEntity.Salary, DbType.Decimal);
            parameters.Add("?FullTime", adminEntity.FullTime, DbType.Boolean);
            parameters.Add("?SalaryCard", adminEntity.SalaryCard, DbType.AnsiString, size: 30);
            parameters.Add("?MaternityLeave", adminEntity.MaternityLeave, DbType.Boolean);
            parameters.Add("?MaternityLeaveStartTime", adminEntity.MaternityLeaveStartTime, DbType.DateTime);
            parameters.Add("?MaternityLeaveEndTime", adminEntity.MaternityLeaveEndTime, DbType.DateTime);
            parameters.Add("?MaternityLeaveSalary", adminEntity.MaternityLeaveSalary, DbType.Decimal);
            parameters.Add("?AnnualLeave", adminEntity.AnnualLeave, DbType.Int32);
            parameters.Add("?SurplusAnnualLeave", adminEntity.SurplusAnnualLeave, DbType.Int32);
            parameters.Add("?Assignment", adminEntity.Assignment, DbType.Boolean);
            parameters.Add("?AssignmentSubsidy", adminEntity.AssignmentSubsidy, DbType.Decimal);
            parameters.Add("?AssignmentStartTime", adminEntity.AssignmentStartTime, DbType.DateTime);
            parameters.Add("?AssignmentEndTime", adminEntity.AssignmentEndTime, DbType.DateTime);
            parameters.Add("?Preference", adminEntity.Preference, DbType.String);
            parameters.Add("?LogonIp", adminEntity.LogonIp, DbType.AnsiString, size: 50);
            parameters.Add("?AddressIp", adminEntity.AddressIp, DbType.AnsiString, size: 50);
            parameters.Add("?SecurityAmount", adminEntity.SecurityAmount, DbType.Decimal);
            parameters.Add("?PaidSecurityAmount", adminEntity.PaidSecurityAmount, DbType.Decimal);
            parameters.Add("?ChildrenEducationCutAmount", adminEntity.ChildrenEducationCutAmount, DbType.Decimal);
            parameters.Add("?ChildrenEducationStartDate", adminEntity.ChildrenEducationStartDate, DbType.Date);
            parameters.Add("?ChildrenEducationEndDate", adminEntity.ChildrenEducationEndDate, DbType.Date);
            parameters.Add("?ContinueEducationCutAmount", adminEntity.ContinueEducationCutAmount, DbType.Decimal);
            parameters.Add("?ContinueEducationStartDate", adminEntity.ContinueEducationStartDate, DbType.Date);
            parameters.Add("?ContinueEducationEndDate", adminEntity.ContinueEducationEndDate, DbType.Date);
            parameters.Add("?MedicalCutAmount", adminEntity.MedicalCutAmount, DbType.Decimal);
            parameters.Add("?HomeLoanCutAmount", adminEntity.HomeLoanCutAmount, DbType.Decimal);
            parameters.Add("?HomeRentCutAmount", adminEntity.HomeRentCutAmount, DbType.Decimal);
            parameters.Add("?ElderlyCareCutAmount", adminEntity.ElderlyCareCutAmount, DbType.Decimal);

            return await databaseContext.Master.QueryFirstOrDefaultAsync<AdminEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改员工信息
        public async Task<AdminEntity> Update(AdminEntity adminEntity)
        {
            string sql = SqlCommands.GetUpdateSql("Admin", new[] { "LoginName", "Password", "FullName", "CompanyId", "CompanyName","AccountStatus","DepartmentName","DepartmentId",
                "Phone", "HomeAddress","IdCard","Introduction","Avatar","Sex","Married","EmergencyContact","EmergencyPhone","FamilyNumber","ChildrenNumber",
                "Education","School","GraduationTime","WorkingYears","EnglishLevel","Trainee","JobDuty","JobTitle","JobDutyKey","JobTitleKey","JobLevel","EmployedDate","LeaveTime",
                "EnglishReadLevel","EnglishWriteLevel","EnglishConversationLevel","Salary","FullTime","SalaryCard","MaternityLeave","MaternityLeaveStartTime",
                "MaternityLeaveEndTime","MaternityLeaveSalary","AnnualLeave","SurplusAnnualLeave","Assignment","AssignmentSubsidy",
                "AssignmentStartTime","AssignmentEndTime","Preference","SecurityAmount","PaidSecurityAmount",
                "ChildrenEducationCutAmount","ChildrenEducationStartDate","ChildrenEducationEndDate",
                "ContinueEducationCutAmount","ContinueEducationStartDate","ContinueEducationEndDate",
                "MedicalCutAmount","HomeLoanCutAmount", "HomeRentCutAmount","ElderlyCareCutAmount"}, new[] { "AdminId", "AccountStatus" });
            sql = string.Format(@"{0} {1} WHERE AdminId=?AdminId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminEntity.AdminId, DbType.Int64);
            parameters.Add("?CompanyId", adminEntity.CompanyId, DbType.Int32, size: 10);
            parameters.Add("?CompanyName", adminEntity.CompanyName, DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentId", adminEntity.DepartmentId, DbType.Int32, size: 10);
            parameters.Add("?DepartmentName", adminEntity.DepartmentName, DbType.AnsiString, size: 50);
            parameters.Add("?LoginName", adminEntity.LoginName, DbType.AnsiString, size: 30);
            parameters.Add("?Password", adminEntity.Password, DbType.AnsiString, size: 255);
            parameters.Add("?FullName", adminEntity.FullName, DbType.AnsiString, size: 20);
            parameters.Add("?AccountStatus", AccountStatus.Using.ToString(), DbType.AnsiString);
            parameters.Add("?Phone", adminEntity.Phone, DbType.AnsiString, size: 20);
            parameters.Add("?HomeAddress", adminEntity.HomeAddress, DbType.AnsiString, size: 200);
            parameters.Add("?IdCard", adminEntity.IdCard, DbType.AnsiString, size: 18);
            parameters.Add("?Introduction", adminEntity.Introduction, DbType.String);
            parameters.Add("?Avatar", adminEntity.Avatar, DbType.AnsiString, size: 1000);
            parameters.Add("?Sex", adminEntity.Sex.ToString(), DbType.AnsiString);
            parameters.Add("?Married", adminEntity.Married, DbType.Boolean);
            parameters.Add("?EmergencyContact", adminEntity.EmergencyContact, DbType.AnsiString, size: 30);
            parameters.Add("?EmergencyPhone", adminEntity.EmergencyPhone, DbType.AnsiString, size: 20);
            parameters.Add("?FamilyNumber", adminEntity.FamilyNumber, DbType.Int32);
            parameters.Add("?ChildrenNumber", adminEntity.ChildrenNumber, DbType.Int32);
            parameters.Add("?Education", adminEntity.Education.ToString(), DbType.AnsiString);
            parameters.Add("?School", adminEntity.School, DbType.AnsiString, size: 50);
            parameters.Add("?GraduationTime", adminEntity.GraduationTime, DbType.DateTime);
            parameters.Add("?WorkingYears", adminEntity.WorkingYears, DbType.Decimal);
            parameters.Add("?EnglishLevel", adminEntity.EnglishLevel.ToString(), DbType.AnsiString);
            parameters.Add("?Trainee", adminEntity.Trainee, DbType.Boolean);
            parameters.Add("?JobDuty", adminEntity.JobDuty, DbType.AnsiString, size: 50);
            parameters.Add("?JobTitle", adminEntity.JobTitle, DbType.AnsiString, size: 50);
            parameters.Add("?JobDutyKey", adminEntity.JobDutyKey, DbType.AnsiString, size: 150);
            parameters.Add("?JobTitleKey", adminEntity.JobTitleKey, DbType.AnsiString, size: 150);
            parameters.Add("?JobLevel", adminEntity.JobLevel, DbType.Int32);
            parameters.Add("?EmployedDate", adminEntity.EmployedDate, DbType.DateTime);
            parameters.Add("?LeaveTime", adminEntity.LeaveTime, DbType.DateTime);
            parameters.Add("?EnglishReadLevel", adminEntity.EnglishReadLevel, DbType.Int32);
            parameters.Add("?EnglishWriteLevel", adminEntity.EnglishWriteLevel, DbType.Int32);
            parameters.Add("?EnglishConversationLevel", adminEntity.EnglishConversationLevel, DbType.Int32);
            parameters.Add("?Salary", adminEntity.Salary, DbType.Decimal);
            parameters.Add("?FullTime", adminEntity.FullTime, DbType.Boolean);
            parameters.Add("?SalaryCard", adminEntity.SalaryCard, DbType.AnsiString, size: 30);
            parameters.Add("?MaternityLeave", adminEntity.MaternityLeave, DbType.Boolean);
            parameters.Add("?MaternityLeaveStartTime", adminEntity.MaternityLeaveStartTime, DbType.DateTime);
            parameters.Add("?MaternityLeaveEndTime", adminEntity.MaternityLeaveEndTime, DbType.DateTime);
            parameters.Add("?MaternityLeaveSalary", adminEntity.MaternityLeaveSalary, DbType.Decimal);
            parameters.Add("?AnnualLeave", adminEntity.AnnualLeave, DbType.Int32);
            parameters.Add("?SurplusAnnualLeave", adminEntity.SurplusAnnualLeave, DbType.Int32);
            parameters.Add("?Assignment", adminEntity.Assignment, DbType.Boolean);
            parameters.Add("?AssignmentSubsidy", adminEntity.AssignmentSubsidy, DbType.Decimal);
            parameters.Add("?AssignmentStartTime", adminEntity.AssignmentStartTime, DbType.DateTime);
            parameters.Add("?AssignmentEndTime", adminEntity.AssignmentEndTime, DbType.DateTime);
            parameters.Add("?Preference", adminEntity.Preference, DbType.String);
            parameters.Add("?SecurityAmount", adminEntity.SecurityAmount, DbType.Decimal);
            parameters.Add("?PaidSecurityAmount", adminEntity.PaidSecurityAmount, DbType.Decimal);
            parameters.Add("?ChildrenEducationCutAmount", adminEntity.ChildrenEducationCutAmount, DbType.Decimal);
            parameters.Add("?ChildrenEducationStartDate", adminEntity.ChildrenEducationStartDate, DbType.Date);
            parameters.Add("?ChildrenEducationEndDate", adminEntity.ChildrenEducationEndDate, DbType.Date);
            parameters.Add("?ContinueEducationCutAmount", adminEntity.ContinueEducationCutAmount, DbType.Decimal);
            parameters.Add("?ContinueEducationStartDate", adminEntity.ContinueEducationStartDate, DbType.Date);
            parameters.Add("?ContinueEducationEndDate", adminEntity.ContinueEducationEndDate, DbType.Date);
            parameters.Add("?MedicalCutAmount", adminEntity.MedicalCutAmount, DbType.Decimal);
            parameters.Add("?HomeLoanCutAmount", adminEntity.HomeLoanCutAmount, DbType.Decimal);
            parameters.Add("?HomeRentCutAmount", adminEntity.HomeRentCutAmount, DbType.Decimal);
            parameters.Add("?ElderlyCareCutAmount", adminEntity.ElderlyCareCutAmount, DbType.Decimal);
          
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AdminEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        public void UpdateAdminLogonInfo(AdminEntity adminEntity)
        {
            string sql = @"UPDATE Admin SET
                                 LastLoginTime=CurrentlyLoginTime,
                                CurrentlyLoginTime=?CurrentlyLoginTime,
                                LogonIp=?LogonIp,
                                AddressIp=?AddressIp
                            WHERE AdminId=?AdminId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminEntity.AdminId, DbType.Int64, size: 20);
            parameters.Add("?CurrentlyLoginTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?LogonIp", adminEntity.LogonIp, DbType.AnsiString, size: 50);
            parameters.Add("?AddressIp", adminEntity.AddressIp, DbType.AnsiString, size: 50);
            databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }
        
        public async Task<AdminEntity> GetAdmin(long adminId)
        {
            string sql = @"{0} WHERE AdminId=?AdminId And AccountStatus='Using'";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            var result = await databaseContext.Slave.QueryFirstOrDefaultAsync<AdminEntity>(string.Format(sql, QUERY), parameters);
            return result;
        }
        
        public async Task<AdminEntity> GetAdmin(string loginName)
        {
            string sql = @"{0} WHERE LoginName=?LoginName AND AccountStatus='Using'";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?LoginName", loginName, DbType.AnsiString, size: 30);
            var result = await databaseContext.Slave.QueryFirstOrDefaultAsync<AdminEntity>(string.Format(sql, QUERY), parameters);
            return result;
        }

        public async Task<IEnumerable<AdminEntity>> GetAdmins(long? adminId, int? companyId, int? departmentId, string jobDutyKey, string jobTitleKey)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            wheres.Add("AccountStatus = 'Using'");

            if (adminId != null)
            {
                wheres.Add("AdminId = ?AdminId");
                parameters.Add("?AdminId", adminId, DbType.Int64);
            }

            if (companyId != null)
            {
                wheres.Add("CompanyId = ?CompanyId");
                parameters.Add("?CompanyId", companyId, DbType.Int32);
            }

            if (departmentId != null)
            {
                wheres.Add("DepartmentId = ?DepartmentId");
                parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            }

            if (!string.IsNullOrWhiteSpace(jobDutyKey))
            {
                wheres.Add("JobDutyKey = ?JobDutyKey");
                parameters.Add("?JobDutyKey", jobDutyKey, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(jobTitleKey))
            {
                wheres.Add("JobTitleKey = ?JobTitleKey");
                parameters.Add("?JobTitleKey", jobTitleKey, DbType.AnsiString);
            }

            return await databaseContext.Slave.QueryAsync<AdminEntity>($"{QUERY} WHERE {string.Join(" AND ", wheres)} GROUP BY AdminId", parameters);
        }

        public async Task<(IEnumerable<AdminEntity>, int)> GetAdmins(int? companyId, int? departmentId, string loginName, string fullName, AccountStatus? accountStatus, string sortName, bool? ascending, int pageIndex = 1, int pageSize = 10)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("IsSystemAccount = 0");

            if (companyId.HasValue)
            {
                wheres.Add("CompanyId=?CompanyId");
                parameters.Add("?CompanyId", companyId, DbType.Int32, size: 11);
            }

            if (departmentId.HasValue)
            {
                wheres.Add("DepartmentId=?DepartmentId");
                parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 10);
            }

            if (accountStatus.HasValue)
            {
                wheres.Add("AccountStatus=?AccountStatus");
                parameters.Add("?AccountStatus", accountStatus.Value.ToString(), DbType.AnsiString);
            }
            else
            {
                wheres.Add("AccountStatus='Using'");
            }

            if (!string.IsNullOrWhiteSpace(loginName))
            {
                wheres.Add("LoginName=?LoginName");
                parameters.Add("?LoginName", loginName, DbType.AnsiString, size: 30);
            }

            if (!string.IsNullOrWhiteSpace(fullName))
            {
                wheres.Add("FullName=?FullName");
                parameters.Add("?FullName", fullName, DbType.AnsiString, size: 20);
            }

            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; 
                                SELECT Count(1) FROM Admin {1}",
                            QUERY,
                            wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                            (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                            pageSize,
                            string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                            ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await databaseContext.Slave.QueryMultipleAsync<AdminEntity, int>(sql, parameters);
        }
        
        public async Task<AdminEntity> SetAdminAccountStatus(long adminId, AccountStatus accountStatus)
        {
            string updateSql = SqlCommands.GetUpdateSql("Admin", new[] { "AccountStatus" }, new[] { "AdminId" });
            string querySql = string.Format(@"{0} WHERE AdminId=?AdminId", QUERY);
            string sql = string.Format("{0}{1} LIMIT 1;", updateSql, querySql);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            parameters.Add("?AccountStatus", accountStatus.ToString(), DbType.AnsiString, size: 20);
            return await databaseContext.Master.QueryFirstOrDefaultAsync<AdminEntity>(sql, parameters, databaseContext.Transaction);
        }
        
        public async Task<bool> UpdateAdminFullNameAndPassword(AdminEntity adminEntity)
        {
            string sql = SqlCommands.GetUpdateSql("Admin", new[] { "FullName", "Password", "Avatar" }, new[] { "AdminId", "AccountStatus" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FullName", adminEntity.FullName, DbType.AnsiString, size: 20);
            parameters.Add("?Password", adminEntity.Password, DbType.AnsiString, size: 255);
            parameters.Add("?Avatar", adminEntity.Avatar, DbType.AnsiString, size: 1000);
            parameters.Add("?AdminId", adminEntity.AdminId, DbType.Int64, size: 10);
            parameters.Add("?AccountStatus", AccountStatus.Using.ToString(), DbType.AnsiString, size: 20);
            var result = await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return result > 0;
        }
        
        public async Task<bool> UpdateAdminAvatar(AdminEntity adminEntity)
        {
            string sql = SqlCommands.GetUpdateSql("Admin", new[] { "Avatar" }, new[] { "AdminId", "AccountStatus" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Avatar", adminEntity.Avatar, DbType.AnsiString, size: 1000);
            parameters.Add("?AdminId", adminEntity.AdminId, DbType.Int64, size: 20);
            parameters.Add("?AccountStatus", AccountStatus.Using.ToString(), DbType.AnsiString, size: 20);
            var result = await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            return result > 0;
        }

        public Task<IEnumerable<AdminEntity>> GetAdminsForCompany()
        {
            string sql = string.Format(@"{0} WHERE AccountStatus='Using'", QUERY);
            return databaseContext.Slave.QueryAsync<AdminEntity>(sql);
        }

        public async Task<bool> UpdateAdminCompanyId(long adminId, int companyId)
        {
            string sql = SqlCommands.GetUpdateSql("Admin", new string[] { "CompanyId" }, new string[] { "AdminId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateAdminJobTitleAndJobDuty(AdminEntity adminEntity)
        {
            string sql = SqlCommands.GetUpdateSql("Admin", new string[] { "JobTitle", "JobTitleKey", "JobDuty", "JobDutyKey" }, new string[] { "AdminId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminEntity.AdminId, DbType.Int64, size: 20);
            parameters.Add("?JobTitle", adminEntity.JobTitle, DbType.AnsiString, size: 50);
            parameters.Add("?JobTitleKey", adminEntity.JobTitleKey, DbType.AnsiString, size: 150);
            parameters.Add("?JobDuty", adminEntity.JobDuty, DbType.AnsiString, size: 50);
            parameters.Add("?JobDutyKey", adminEntity.JobDutyKey, DbType.AnsiString, size: 150);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdatePaidSecurityAmount(AdminEntity adminEntity)
        {
            string sql = SqlCommands.GetUpdateSql("Admin", new string[] { "PaidSecurityAmount" }, new string[] { "AdminId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminEntity.AdminId, DbType.Int64, size: 20);
            parameters.Add("?PaidSecurityAmount", adminEntity.PaidSecurityAmount, DbType.Decimal, size: 12);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<IEnumerable<AdminEntity>> GetCompanyAdminsAsync(int companyId)
        {
            string sql = $" {QUERY} WHERE CompanyId=?CompanyId AND AccountStatus='Using'";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int64, size: 20);
            return await databaseContext.Slave.QueryAsync<AdminEntity>(sql, parameters);
        }

        public async Task<IEnumerable<AdminEntity>> GetCompanyAdminsAsync(string jobTitleKey)
        {
            string sql = $" {QUERY} WHERE JobTitleKey=?JobTitleKey AND AccountStatus='Using'";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?JobTitleKey", jobTitleKey, DbType.AnsiString);
            return await databaseContext.Slave.QueryAsync<AdminEntity>(sql, parameters);
        }

        public async Task<IEnumerable<long>> GetByDepartmentIdAsync(string departmentIdPath)
        {
            string sql = @"SELECT AdminId FROM Admin WHERE FIND_IN_SET(DepartmentId,?DepartmentIdPath)";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentIdPath", departmentIdPath, DbType.AnsiString);
            return await databaseContext.Slave.QueryAsync<long>(sql, parameters);
        }

        public async Task<bool> UpdateDepartmentInfo(long adminId, int companyId, string companyName, int departmentId, string departmentName)
        {
            string sql = SqlCommands.GetUpdateSql("Admin", new string[] { "CompanyId", "CompanyName", "DepartmentId", "DepartmentName" }, new string[] { "AdminId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            parameters.Add("?CompanyName", companyName, DbType.AnsiString);
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            parameters.Add("?DepartmentName", departmentName, DbType.AnsiString);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters,databaseContext.Transaction) > 0;
        }

        public async Task<AdminEntity> GetAdminByAgentRecordId(long agentRecordId)
        {
            var sql = $@"{QUERY_A} INNER JOIN company B ON A.AdminId=B.CompanyAdminId
                            INNER JOIN agentrecord C ON B.CompanyId=C.CompanyId WHERE C.AgentRecordId=?AgentRecordId";
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AdminEntity>(sql, new { agentRecordId });
        }
    }
}
